package com.jian.java;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Properties;

/**
 * @author Jiange
 * @create 2021-04-04-19:26
 *  这里的BeanHandler里的对象要有空参构造器和getset方法
 */
public class DBUtilTest {
    @Test
    public void test1() throws Exception {
        QueryRunner queryRunner = new QueryRunner();

        Properties properties = new Properties();
        FileInputStream fileInputStream = new FileInputStream("Durid.properties");
        properties.load(fileInputStream);

        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        int jian = queryRunner.update(connection, "update `students` set dept=? where sno=?", "IS",5);
        System.out.println("改变了"+jian+"条数据");
    }
    @Test
    public void test2() throws Exception {
        QueryRunner queryRunner = new QueryRunner();
        Properties properties = new Properties();
        properties.load(new FileInputStream("Durid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
//        queryRunner.query(connection,"",new BeanHandler<>(Person.class),"");
//        DbUtils.commitAndCloseQuietly(connection);
//        BeanListHandler<Jian> beanListHandler = new BeanListHandler<>(Jian.class);
//        List<Jian> query = queryRunner.query(connection, "select sname from `students`",beanListHandler);
//        query.forEach(System.out::println);
//        BeanHandler<Jian> beanHandler = new BeanHandler<>(Jian.class);
//
//        Jian select_sname_from_students = queryRunner.query(connection, "select Sname from `students` where Sno=?", beanHandler,1);
//        System.out.println(select_sname_from_students);

        Object query = queryRunner.query(connection, "select count(*) from students where Sno<4", new ScalarHandler<>());
        System.out.println(query);
        connection.close();
    }
    @Test
    public void test3() throws Exception {
        Properties properties = new Properties();
        properties.load(new FileInputStream("Durid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        PreparedStatement preparedStatement = connection.prepareStatement("");
//        preparedStatement.setBlob(1,);
        ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
        int parameterCount = parameterMetaData.getParameterCount();
        ResultSet resultSet = preparedStatement.executeQuery();
        connection.setAutoCommit(false);
        int transactionIsolation = connection.getTransactionIsolation();
        connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        preparedStatement.addBatch();
        preparedStatement.executeBatch();
        preparedStatement.clearBatch();
        connection.commit();
    }
}
